// Package repository implements database access for transactions and statistics.
package repository

import (
	"context"
	"time"

	"github.com/we-pay-kit/we-pay-kit/internal/db"
)

type TransactionRecord struct {
	ID              int64
	TransactionID   string
	ActivityID      *int64
	PhoneNumber     *string
	UserID          *string
	OrderAmount     float64
	DiscountAmount  float64
	ActualPayAmount float64
	TransactionTime time.Time
	TransactionType string
	Status          string
	PaymentChannel  string
	CouponID        *string
}

type TransactionRepository struct{ db *db.DB }

func NewTransactionRepository(database *db.DB) *TransactionRepository {
	return &TransactionRepository{db: database}
}

func (r *TransactionRepository) Create(ctx context.Context, t *TransactionRecord) (int64, error) {
	q := `INSERT INTO transaction_record(transaction_id, activity_id, phone_number, user_id, order_amount, discount_amount, actual_pay_amount, transaction_time, transaction_type, status, payment_channel, coupon_id)
          VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING id`
	var id int64
	err := r.db.QueryRowContext(ctx, q,
		t.TransactionID, t.ActivityID, t.PhoneNumber, t.UserID, t.OrderAmount, t.DiscountAmount, t.ActualPayAmount, t.TransactionTime, t.TransactionType, t.Status, t.PaymentChannel, t.CouponID,
	).Scan(&id)
	return id, err
}

func (r *TransactionRepository) UpdateStatus(ctx context.Context, transactionID, status string) error {
	q := `UPDATE transaction_record SET status=$1 WHERE transaction_id=$2`
	_, err := r.db.ExecContext(ctx, q, status, transactionID)
	return err
}

func (r *TransactionRepository) GetByTransactionID(ctx context.Context, transactionID string) (*TransactionRecord, error) {
	q := `SELECT id, transaction_id, activity_id, phone_number, user_id, order_amount, discount_amount, actual_pay_amount, transaction_time, transaction_type, status, payment_channel, coupon_id
          FROM transaction_record WHERE transaction_id=$1`
	row := r.db.QueryRowContext(ctx, q, transactionID)
	var t TransactionRecord
	var aid *int64
	var phone, uid, coupon *string
	if err := row.Scan(&t.ID, &t.TransactionID, &aid, &phone, &uid, &t.OrderAmount, &t.DiscountAmount, &t.ActualPayAmount, &t.TransactionTime, &t.TransactionType, &t.Status, &t.PaymentChannel, &coupon); err != nil {
		return nil, err
	}
	t.ActivityID = aid
	t.PhoneNumber = phone
	t.UserID = uid
	t.CouponID = coupon
	return &t, nil
}

func (r *TransactionRepository) List(ctx context.Context, limit, offset int) ([]TransactionRecord, error) {
	if limit <= 0 {
		limit = 20
	}
	q := `SELECT id, transaction_id, activity_id, phone_number, user_id, order_amount, discount_amount, actual_pay_amount, transaction_time, transaction_type, status, payment_channel, coupon_id
          FROM transaction_record ORDER BY id DESC LIMIT $1 OFFSET $2`
	rows, err := r.db.QueryContext(ctx, q, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	res := make([]TransactionRecord, 0, limit)
	for rows.Next() {
		var t TransactionRecord
		var aid *int64
		var phone, uid, coupon *string
		if err := rows.Scan(&t.ID, &t.TransactionID, &aid, &phone, &uid, &t.OrderAmount, &t.DiscountAmount, &t.ActualPayAmount, &t.TransactionTime, &t.TransactionType, &t.Status, &t.PaymentChannel, &coupon); err != nil {
			return nil, err
		}
		t.ActivityID = aid
		t.PhoneNumber = phone
		t.UserID = uid
		t.CouponID = coupon
		res = append(res, t)
	}
	return res, rows.Err()
}

// Stats aggregates total counts and amounts by date range.
type TransactionStats struct {
	TotalCount    int     `json:"total_count"`
	TotalAmount   float64 `json:"total_amount"`
	TotalDiscount float64 `json:"total_discount"`
	TotalActual   float64 `json:"total_actual"`
}

func (r *TransactionRepository) StatsByRange(ctx context.Context, from, to time.Time) (*TransactionStats, error) {
	q := `SELECT COUNT(1), COALESCE(SUM(order_amount),0), COALESCE(SUM(discount_amount),0), COALESCE(SUM(actual_pay_amount),0)
          FROM transaction_record WHERE transaction_time >= $1 AND transaction_time < $2`
	var s TransactionStats
	if err := r.db.QueryRowContext(ctx, q, from, to).Scan(&s.TotalCount, &s.TotalAmount, &s.TotalDiscount, &s.TotalActual); err != nil {
		return nil, err
	}
	return &s, nil
}
